package cn.hwua.dao.impl;

import cn.hwua.dao.UserDao;
import cn.hwua.entity.User;
import cn.hwua.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {
    Connection conn=DBUtil.getConnection();
    QueryRunner qr=new QueryRunner();
    @Override
    public int save(User obj) {
        String sql="INSERT INTO  tb_users(user_id,user_name,user_pwd,user_phone,user_email,user_sex,user_img,user_addtime,user_desc,user_role) VALUES (?,?,?,?,?,?,?,?,?,?)";
        Object[] paras={obj.getUser_id(),obj.getUser_name(),obj.getUser_pwd(),obj.getUser_phone(),
                obj.getUser_email(),obj.getUser_sex(),obj.getUser_img(),obj.getUser_addtime(),
                obj.getUser_desc(),obj.getUser_role()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(User obj) {
        String sql="UPDATE  tb_users SET  user_name=?,user_pwd=?,user_phone=?,user_email=?,user_sex=?,user_img=?,user_addtime=?,user_desc=?,user_role=? WHERE user_id=? ";
        Object[] paras={obj.getUser_name(),obj.getUser_pwd(),obj.getUser_phone(),
                obj.getUser_email(),obj.getUser_sex(),obj.getUser_img(),obj.getUser_addtime(),
                obj.getUser_desc(),obj.getUser_role(),obj.getUser_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(User obj) {
        String sql="DELETE FROM tb_users WHERE user_id  = ? ";
        Object[] paras={obj.getUser_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User findById(Object id) {
        String sql="SELECT * FROM tb_users where user_id  = ?";
        Object[] paras={id};
        try {
            User user=qr.query(conn,sql,paras,new BeanHandler<User>(User.class));
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> findAll() {
        String sql="SELECT * FROM tb_users";
        Object[] paras={};
        try {
            List<User> userList=qr.query(conn,sql,paras,new BeanListHandler<User>(User.class));
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> findSearch(String keyword) {
        String sql="SELECT * FROM tb_users where user_name like concat('%',concat(?,'%'))";
        Object[] paras={keyword};
        try {
            List<User> userList=qr.query(conn,sql,paras,new BeanListHandler<User>(User.class));
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> findPage(int page, int pagesize) {
        String sql="SELECT * FROM tb_users limit ?,?";
        Object[] paras={(page-1)*pagesize,pagesize};
        try {
            List<User> userList=qr.query(conn,sql,paras,new BeanListHandler<User>(User.class));
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql="select count(*) from tb_users";
        Object[] paras={};
        try {
            long nums=qr.query(conn,sql,paras,new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
